﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Serialization;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

using System.Collections;
namespace DAL.SqlHelper
{
    /// <summary>
    /// Common设置
    /// </summary>
    /// <remarks>
    /// <para>作者：cai jiquan</para>
    /// <para>日期:2014.9.19</para>
    /// </remarks>
    [Serializable]
    public class Settings
    {

        #region 静态

        private static Settings _Current;
        /// <summary>
        /// 当前设置
        /// </summary>
        public static Settings Current
        {
            get
            {
                if (_Current == null)
                {
                    _Current = ConfigurationManager.GetSection("SqlConnection") as Settings;
                }
                return _Current;
            }
        }
        /// <summary>
        /// 取得数据库连接
        /// </summary>
        /// <param name="masterServer">是否是主体数据库</param>
        /// <returns>数据库连接</returns>
        public static SqlConnection GetSqlConnection(bool masterServer)
        {
            DatabaseConnection conn = null;
            if (masterServer || Current._DatabaseConnections.Length == 1)
            {
                conn = Current._DatabaseConnections[0];
            }
            else
            {
                conn = Current._DatabaseConnections[new Random().Next(2, Current._DatabaseConnections.Length)];
                //conn = Current._DatabaseConnections[0];
            }
            if (conn.ConnectionStr == null)
                conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3}", conn.Server, conn.Database, conn.User, conn.Password);
            SqlConnection connection = new SqlConnection(conn.ConnectionStr);
            //if (connection.State != ConnectionState.Open)
            //{
            //    connection.Open();
            //}
            return connection;
        }

        public static SqlConnection GetSqlConnection(int index)
        {
            DatabaseConnection conn = null;
            conn = Current._DatabaseConnections[index];
            if (conn.ConnectionStr == null)
                conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3}", conn.Server, conn.Database, conn.User, conn.Password);
            SqlConnection connection = new SqlConnection(conn.ConnectionStr);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return connection;
        }
        #endregion

        #region mysql 数据库连接字符串
        /// <summary>
        /// 取得数据库连接
        /// </summary>
        /// <param name="masterServer">是否是主体数据库</param>
        /// <returns>数据库连接</returns>
        public static MySql.Data.MySqlClient.MySqlConnection GetMySqlConnection(bool masterServer)
        {
            DatabaseConnection conn = null;
            if (masterServer || Current._DatabaseConnections.Length == 1)
            {
                conn = Current._DatabaseConnections[0];
            }
            else
            {
                conn = Current._DatabaseConnections[(new Random().Next(2, Current._DatabaseConnections.Length)) - 1];
                //conn = Current._DatabaseConnections[0];
            }
            if (conn.ConnectionStr == null)
                conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3};charset=utf8;Convert Zero Datetime=True", conn.Server, conn.Database, conn.User, conn.Password);
            //conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3};charset=utf8", SecurityHelper.Safe.AESDecrypt(conn.Server, Common.SecurityHelper.Safe.strConfigAECKey), SecurityHelper.Safe.AESDecrypt(conn.Database, Common.SecurityHelper.Safe.strConfigAECKey), SecurityHelper.Safe.AESDecrypt(conn.User, Common.SecurityHelper.Safe.strConfigAECKey), SecurityHelper.Safe.AESDecrypt(conn.Password, Common.SecurityHelper.Safe.strConfigAECKey));

            MySqlConnection connection = new MySqlConnection(conn.ConnectionStr);
            //if (connection.State != ConnectionState.Open)
            //{
            //    connection.Open();
            //}
            return connection;
        }

        public static MySql.Data.MySqlClient.MySqlConnection GetBrandMySqlConnection(bool masterServer)
        {
            DatabaseConnection conn = null;
            if (masterServer || Current._BrandDatabaseConnections.Length == 1)
            {
                conn = Current._BrandDatabaseConnections[0];
            }
            else
            {
                conn = Current._BrandDatabaseConnections[(new Random().Next(2, Current._BrandDatabaseConnections.Length)) - 1];
            }
            if (conn.ConnectionStr == null)
                conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3};charset=utf8;Convert Zero Datetime=True", conn.Server, conn.Database, conn.User, conn.Password);

            MySqlConnection connection = new MySqlConnection(conn.ConnectionStr);

            return connection;
        }

        /// <summary>
        /// SOP连接
        /// </summary>
        /// <param name="masterServer"></param>
        /// <returns></returns>
        public static MySql.Data.MySqlClient.MySqlConnection GetSOPMySqlConnection(bool masterServer)
        {
            DatabaseConnection conn = null;
            if (masterServer || Current._SOPDatabaseConnections.Length == 1)
            {
                conn = Current._SOPDatabaseConnections[0];
            }
            else
            {
                conn = Current._SOPDatabaseConnections[(new Random().Next(2, Current._SOPDatabaseConnections.Length)) - 1];
            }
            if (conn.ConnectionStr == null)
                conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3};charset=utf8;Convert Zero Datetime=True", conn.Server, conn.Database, conn.User, conn.Password);

            MySqlConnection connection = new MySqlConnection(conn.ConnectionStr);

            return connection;
        }
        /// <summary>
        /// Report连接
        /// </summary>
        /// <param name="masterServer"></param>
        /// <returns></returns>
        public static MySql.Data.MySqlClient.MySqlConnection GetReportMySqlConnection(bool masterServer)
        {
            DatabaseConnection conn = null;
            if (masterServer || Current._ReportDatabaseConnections.Length == 1)
            {
                conn = Current._ReportDatabaseConnections[0];
            }
            else
            {
                conn = Current._ReportDatabaseConnections[(new Random().Next(2, Current._ReportDatabaseConnections.Length)) - 1];
            }
            if (conn.ConnectionStr == null)
                conn.ConnectionStr = string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3};charset=utf8;Convert Zero Datetime=True", conn.Server, conn.Database, conn.User, conn.Password);

            MySqlConnection connection = new MySqlConnection(conn.ConnectionStr);

            return connection;
        }
        #endregion

        #region 数据库连接
        public class DatabaseConnection
        {
            /// <summary>
            /// 初始化数据库连接对象
            /// </summary>
            public DatabaseConnection()
            {
            }
            /// <summary>
            /// 初始化数据库连接对象
            /// </summary>
            /// <param name="server">数据库服务器</param>
            /// <param name="dataBase">默认数据库</param>
            /// <param name="user">用户</param>
            /// <param name="password">密码</param>
            public DatabaseConnection(string server, string dataBase, string user, string password)
            {
                Server = server;
                Database = dataBase;
                User = user;
                Password = password;
            }
            public string Server { get; set; }
            public string Database { get; set; }
            public string User { get; set; }
            public string Password { get; set; }

            internal string ConnectionStr { get; set; }
        }
        #endregion

        private DatabaseConnection[] _DatabaseConnections = new DatabaseConnection[] { new DatabaseConnection("127.0.0.1", "Fyz5897Test", "sa", "123456") };
        public DatabaseConnection[] DatabaseConnections
        {
            get { return _DatabaseConnections; }
            set { _DatabaseConnections = value; }
        }
        private DatabaseConnection[] _BrandDatabaseConnections = new DatabaseConnection[] { new DatabaseConnection("127.0.0.1", "Fyz5897Test", "sa", "123456") };
        public DatabaseConnection[] BrandDatabaseConnections
        {
            get { return _BrandDatabaseConnections; }
            set { _BrandDatabaseConnections = value; }
        }

        private DatabaseConnection[] _SOPDatabaseConnections = new DatabaseConnection[] { new DatabaseConnection("127.0.0.1", "Fyz5897Test", "sa", "123456") };
        public DatabaseConnection[] SOPDatabaseConnections
        {
            get { return _SOPDatabaseConnections; }
            set { _SOPDatabaseConnections = value; }
        }
         private DatabaseConnection[] _ReportDatabaseConnections = new DatabaseConnection[] { new DatabaseConnection("127.0.0.1", "Fyz5897Test", "sa", "123456") };
        public DatabaseConnection[] ReportDatabaseConnections
        {
            get { return _ReportDatabaseConnections; }
            set { _ReportDatabaseConnections = value; }
        }
    }

    /// <summary>
    /// Deserializes the markup in Web.config into an instance of the <see cref="RewriterConfiguration"/> class.
    /// </summary>
    public class ConfigSerializerSectionHandler : IConfigurationSectionHandler
    {
        /// <summary>
        /// Creates an instance of the <see cref="RewriterConfiguration"/> class.
        /// </summary>
        /// <remarks>Uses XML Serialization to deserialize the XML in the Web.config file into an
        /// <see cref="RewriterConfiguration"/> instance.</remarks>
        /// <returns>An instance of the <see cref="RewriterConfiguration"/> class.</returns>
        public object Create(object parent, object configContext, System.Xml.XmlNode section)
        {
            // Create an instance of XmlSerializer based on the RewriterConfiguration type...
            XmlSerializer serializer = new XmlSerializer(typeof(Settings));

            // Return the Deserialized object from the Web.config XML
            return serializer.Deserialize(new System.Xml.XmlNodeReader(section.FirstChild));
        }

    }

    public class SqlHelper
    {
        #region 运营分析

        #region 私有构造函数和方法
        private SqlHelper() { }
        /// <summary>
        /// 将MySqlParameter参数数组(参数值)分配给MySqlCommand命令.
        /// 这个方法将给任何一个参数分配DBNull.Value;
        /// 该操作将阻止默认值的使用.
        /// </summary>
        /// <param name="command">命令名</param>
        /// <param name="commandParameters">SqlParameters数组</param>
        private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (MySqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        /// <summary>
        /// 将DataRow类型的列值分配到MySqlParameter参数数组.
        /// </summary>
        /// <param name="commandParameters">要分配值的MySqlParameter参数数组</param>
        /// <param name="dataRow">将要分配给存储过程参数的DataRow</param>
        private static void AssignParameterValues(MySqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null))
            {
                return;
            }
            int i = 0;
            // 设置参数值
            foreach (MySqlParameter commandParameter in commandParameters)
            {
                // 创建参数名称,如果不存在,只抛出一个异常.
                if (commandParameter.ParameterName == null ||
                    commandParameter.ParameterName.Length <= 1)
                    throw new Exception(
                        string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
                // 从dataRow的表中获取为参数数组中数组名称的列的索引.
                // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数.
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary>
        /// 将一个对象数组分配给MySqlParameter参数数组.
        /// </summary>
        /// <param name="commandParameters">要分配值的MySqlParameter参数数组</param>
        /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param>
        private static void AssignParameterValues(MySqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }
            // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常.
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("参数值个数与参数不匹配.");
            }
            // 给参数赋值
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // If the current array value derives from IDbDataParameter, then assign its Value property
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }

        /// <summary>
        /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
        /// </summary>
        /// <param name="command">要处理的MySqlCommand</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">一个有效的事务或者是null值</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">存储过程名或都T-SQL命令文本</param>
        /// <param name="commandParameters">和命令相关联的MySqlParameter参数数组,如果没有参数为'null'</param>
        /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
        private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }
            // 给命令分配一个数据库连接.
            command.Connection = connection;
            // 设置命令文本(存储过程名或SQL语句)
            command.CommandText = commandText;
            // 分配事务
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }
            // 设置命令类型.
            command.CommandType = commandType;
            // 分配命令参数
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }


        public static string GetMySqlConnectionStr()
        {
            return Settings.GetMySqlConnection(false).ConnectionString;
        }
        #endregion 私有构造函数和方法结束

        #region ExecuteScalar 返回结果集中的第一行第一列
        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            // 执行参数为空的方法
            return ExecuteScalar(connectionString, commandType, commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            // 创建并打开数据库连接对象,操作完成释放对象.
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                // 调用指定数据库连接字符串重载方法.
                return ExecuteScalar(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,指定参数值,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果有参数值
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
                MySqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
                // 给存储过程参数赋值
                AssignParameterValues(commandParameters, parameterValues);
                // 调用重载方法
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 没有参数值
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText)
        {
            // 执行参数为空的方法
            return ExecuteScalar(connection, commandType, commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            // 创建MySqlCommand命令,并进行预处理
            MySqlCommand cmd = new MySqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // 执行MySqlCommand命令,并返回结果.
            object retval = cmd.ExecuteScalar();

            // 清除参数,以便再次使用.
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数值,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(MySqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            // 如果有参数值
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
                MySqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
                // 给存储过程参数赋值
                AssignParameterValues(commandParameters, parameterValues);
                // 调用重载方法
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 没有参数值
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定数据库事务的命令,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="transaction">一个有效的连接事务</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText)
        {
            // 执行参数为空的方法
            return ExecuteScalar(transaction, commandType, commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">一个有效的连接事务</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            // 创建MySqlCommand命令,并进行预处理
            MySqlCommand cmd = new MySqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // 执行MySqlCommand命令,并返回结果.
            object retval = cmd.ExecuteScalar();

            // 清除参数,以便再次使用.
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary>
        /// 执行指定数据库事务的命令,指定参数值,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="transaction">一个有效的连接事务</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(MySqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            // 如果有参数值
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // PPull the parameters for this stored procedure from the parameter cache ()
                MySqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
                // 给存储过程参数赋值
                AssignParameterValues(commandParameters, parameterValues);
                // 调用重载方法
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 没有参数值
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion ExecuteScalar

        #region ExecuteDataset方法
        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="commandParameters">SqlParamters参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            // 创建并打开数据库连接对象,操作完成释放对象.
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                // 调用指定数据库连接字符串重载方法.
                return ExecuteDataset(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,直接提供参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值.
        /// 示例: 
        ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从缓存中检索存储过程参数
                MySqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
                // 给存储过程参数分配值
                AssignParameterValues(commandParameters, parameterValues);
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或T-SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或T-SQL语句</param>
        /// <param name="commandParameters">SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            // 预处理
            MySqlCommand cmd = new MySqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建MySqlDataAdapter和DataSet.
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                // 填充DataSet.
                da.Fill(ds);

                cmd.Parameters.Clear();
                if (mustCloseConnection)
                    connection.Close();
                return ds;
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输入参数和返回值.
        /// 示例.:  
        ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(MySqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 比缓存中加载存储过程参数
                MySqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
                // 给存储过程参数分配值
                AssignParameterValues(commandParameters, parameterValues);
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定事务的命令,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或T-SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(MySqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText, (MySqlParameter[])null);
        }

        /// <summary>
        /// 执行指定事务的命令,指定参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或T-SQL语句</param>
        /// <param name="commandParameters">SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            // 预处理
            MySqlCommand cmd = new MySqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建 DataAdapter & DataSet
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
        }

        /// <summary>
        /// 执行指定事务的命令,指定参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输入参数和返回值.
        /// 示例.:  
        ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">事务</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(MySqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从缓存中加载存储过程参数
                MySqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
                // 给存储过程参数分配值
                AssignParameterValues(commandParameters, parameterValues);
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion ExecuteDataset数据集命令结束

        #endregion

        public static int SqlBulkCopy(DataTable dt, string DataTableName, SqlConnection conn)
        {
            try
            {
                using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
                {
                    //一次批量的插入的数据量
                    sqlBC.BatchSize = dt.Rows.Count;
                    //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                    sqlBC.BulkCopyTimeout = 300;
                    sqlBC.DestinationTableName = DataTableName;
                    //批量写入
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        if (conn.State == ConnectionState.Closed)
                        {
                            conn.Open();
                        }
                        sqlBC.WriteToServer(dt);
                    }
                    sqlBC.Close();
                    return 5;
                }
            }
            catch (Exception ex)
            {
#if DEBUG
                throw ex;
#else
                   return -10000;
#endif
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="tblName">表名</param>
        /// <param name="strGetFields">需要查询的字段</param>
        /// <param name="key">主键</param>
        /// <param name="PageSize">PageSize</param>
        /// <param name="PageIndex">PageIndex</param>
        /// <param name="OrderType">排序字段和规则</param>
        /// <param name="strWhere">条件</param>
        /// <param name="Count">返回总条数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetPageByBossID(string tblName, string strGetFields, string key, int PageSize, int PageIndex, string OrderType, string strWhere, out int Count)
        {
            Count = 0;
            if (tblName == string.Empty)
                return null;
            if (OrderType == string.Empty)
                return null;
            if (strWhere == string.Empty)
                return null;
            using (MySqlConnection conn = Settings.GetMySqlConnection(false))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand("P_GetPageByBossID", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tblName", MySqlDbType.VarChar, 500).Value = tblName;
                    cmd.Parameters.Add("@strGetFields", MySqlDbType.VarChar, 1000).Value = strGetFields;
                    cmd.Parameters.Add("@SelKey", MySqlDbType.VarChar, 25).Value = key;
                    cmd.Parameters.Add("@PageSize", MySqlDbType.Int32).Value = PageSize;
                    cmd.Parameters.Add("@PageIndex", MySqlDbType.Int32).Value = PageIndex;
                    cmd.Parameters.Add("@OrderType", MySqlDbType.VarChar, 255).Value = OrderType;
                    cmd.Parameters.Add("@strWhere", MySqlDbType.VarChar, 1500).Value = strWhere;
                    cmd.Parameters.Add("@Count", MySqlDbType.Int32).Value = Count;
                    cmd.Parameters["@Count"].Direction = ParameterDirection.Output;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    DataSet ds = new System.Data.DataSet();
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                    sda.Fill(ds);

                    Object ObjCount = cmd.Parameters["@Count"].Value;
                    Count = ObjCount == null || ObjCount.ToString() == string.Empty ? 0 : (int)ObjCount;
                    if (ds.Tables.Count > 0)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
#if DEBUG
                    throw ex;
#else
                   return null;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="tblName">表名</param>
        /// <param name="strGetFields">需要查询的字段</param>
        /// <param name="key">主键</param>
        /// <param name="PageSize">PageSize</param>
        /// <param name="PageIndex">PageIndex</param>
        /// <param name="OrderType">排序字段和规则</param>
        /// <param name="strWhere">条件</param>
        /// <param name="Count">返回总条数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetBrandPageByBossID(string tblName, string strGetFields, string key, int PageSize, int PageIndex, string OrderType, string strWhere, out int Count)
        {
            Count = 0;
            if (tblName == string.Empty)
                return null;
            if (OrderType == string.Empty)
                return null;
            if (strWhere == string.Empty)
                return null;
            using (MySqlConnection conn = Settings.GetBrandMySqlConnection(false))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand("P_GetPageByBossID", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tblName", MySqlDbType.VarChar, 500).Value = tblName;
                    cmd.Parameters.Add("@strGetFields", MySqlDbType.VarChar, 1000).Value = strGetFields;
                    cmd.Parameters.Add("@SelKey", MySqlDbType.VarChar, 25).Value = key;
                    cmd.Parameters.Add("@PageSize", MySqlDbType.Int32).Value = PageSize;
                    cmd.Parameters.Add("@PageIndex", MySqlDbType.Int32).Value = PageIndex;
                    cmd.Parameters.Add("@OrderType", MySqlDbType.VarChar, 255).Value = OrderType;
                    cmd.Parameters.Add("@strWhere", MySqlDbType.VarChar, 1500).Value = strWhere;
                    cmd.Parameters.Add("@Count", MySqlDbType.Int32).Value = Count;
                    cmd.Parameters["@Count"].Direction = ParameterDirection.Output;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    DataSet ds = new System.Data.DataSet();
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                    sda.Fill(ds);

                    Object ObjCount = cmd.Parameters["@Count"].Value;
                    Count = ObjCount == null || ObjCount.ToString() == string.Empty ? 0 : (int)ObjCount;
                    if (ds.Tables.Count > 0)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
#if DEBUG
                    throw ex;
#else
                    return null;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="tblName">表名</param>
        /// <param name="strGetFields">需要查询的字段</param>
        /// <param name="key">主键</param>
        /// <param name="PageSize">PageSize</param>
        /// <param name="PageIndex">PageIndex</param>
        /// <param name="OrderType">排序字段和规则</param>
        /// <param name="strWhere">条件</param>
        /// <param name="Count">返回总条数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetSOPPageByBossID(string tblName, string strGetFields, string key, int PageSize, int PageIndex, string OrderType, string strWhere, out int Count)
        {
            Count = 0;
            if (tblName == string.Empty)
                return null;
            if (OrderType == string.Empty)
                return null;
            if (strWhere == string.Empty)
                return null;
            using (MySqlConnection conn = Settings.GetSOPMySqlConnection(false))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand("P_GetPageByBossID", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tblName", MySqlDbType.VarChar, 500).Value = tblName;
                    cmd.Parameters.Add("@strGetFields", MySqlDbType.VarChar, 1000).Value = strGetFields;
                    cmd.Parameters.Add("@SelKey", MySqlDbType.VarChar, 25).Value = key;
                    cmd.Parameters.Add("@PageSize", MySqlDbType.Int32).Value = PageSize;
                    cmd.Parameters.Add("@PageIndex", MySqlDbType.Int32).Value = PageIndex;
                    cmd.Parameters.Add("@OrderType", MySqlDbType.VarChar, 255).Value = OrderType;
                    cmd.Parameters.Add("@strWhere", MySqlDbType.VarChar, 1500).Value = strWhere;
                    cmd.Parameters.Add("@Count", MySqlDbType.Int32).Value = Count;
                    cmd.Parameters["@Count"].Direction = ParameterDirection.Output;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    DataSet ds = new System.Data.DataSet();
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                    sda.Fill(ds);

                    Object ObjCount = cmd.Parameters["@Count"].Value;
                    Count = ObjCount == null || ObjCount.ToString() == string.Empty ? 0 : (int)ObjCount;
                    if (ds.Tables.Count > 0)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
#if DEBUG
                    throw ex;
#else
                   return null;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }


    /// <summary>
    /// SqlHelperParameterCache提供缓存存储过程参数,并能够在运行时从存储过程中探索参数.
    /// </summary>
    public sealed class SqlHelperParameterCache
    {
        #region 私有方法,字段,构造函数
        // 私有构造函数,妨止类被实例化.
        private SqlHelperParameterCache() { }
        // 这个方法要注意
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        /// <summary>
        /// 探索运行时的存储过程,返回MySqlParameter参数数组.
        /// 初始化参数值为 DBNull.Value.
        /// </summary>
        /// <param name="connection">一个有效的数据库连接</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>返回MySqlParameter参数数组</returns>
        private static MySqlParameter[] DiscoverSpParameterSet(MySqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            MySqlCommand cmd = new MySqlCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            connection.Open();
            // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
            MySqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();
            // 如果不包含返回值参数,将参数集中的每一个参数删除.
            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            // 创建参数数组
            MySqlParameter[] discoveredParameters = new MySqlParameter[cmd.Parameters.Count];
            // 将cmd的Parameters参数集复制到discoveredParameters数组.
            cmd.Parameters.CopyTo(discoveredParameters, 0);
            // 初始化参数值为 DBNull.Value.
            foreach (MySqlParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary>
        /// MySqlParameter参数数组的深层拷贝.
        /// </summary>
        /// <param name="originalParameters">原始参数数组</param>
        /// <returns>返回一个同样的参数数组</returns>
        private static MySqlParameter[] CloneParameters(MySqlParameter[] originalParameters)
        {
            MySqlParameter[] clonedParameters = new MySqlParameter[originalParameters.Length];
            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (MySqlParameter)((ICloneable)originalParameters[i]).Clone();
            }
            return clonedParameters;
        }
        #endregion 私有方法,字段,构造函数结束

        #region 缓存方法
        /// <summary>
        /// 追加参数数组到缓存.
        /// </summary>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="commandParameters">要缓存的参数数组</param>
        public static void CacheParameterSet(string connectionString, string commandText, params MySqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
            string hashKey = connectionString + ":" + commandText;
            paramCache[hashKey] = commandParameters;
        }

        /// <summary>
        /// 从缓存中获取参数数组.
        /// </summary>
        /// <param name="connectionString">一个有效的数据库连接字符</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <returns>参数数组</returns>
        public static MySqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
            string hashKey = connectionString + ":" + commandText;
            MySqlParameter[] cachedParameters = paramCache[hashKey] as MySqlParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }
        #endregion 缓存方法结束

        #region 检索指定的存储过程的参数集
        /// <summary>
        /// 返回指定的存储过程的参数集
        /// </summary>
        /// <remarks>
        /// 这个方法将查询数据库,并将信息存储到缓存.
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符</param>
        /// <param name="spName">存储过程名</param>
        /// <returns>返回MySqlParameter参数数组</returns>
        public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        /// <summary>
        /// 返回指定的存储过程的参数集
        /// </summary>
        /// <remarks>
        /// 这个方法将查询数据库,并将信息存储到缓存.
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符.</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>返回MySqlParameter参数数组</returns>
        public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// [内部]返回指定的存储过程的参数集(使用连接对象).
        /// </summary>
        /// <remarks>
        /// 这个方法将查询数据库,并将信息存储到缓存.
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接字符</param>
        /// <param name="spName">存储过程名</param>
        /// <returns>返回MySqlParameter参数数组</returns>
        internal static MySqlParameter[] GetSpParameterSet(MySqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        /// <summary>
        /// [内部]返回指定的存储过程的参数集(使用连接对象)
        /// </summary>
        /// <remarks>
        /// 这个方法将查询数据库,并将信息存储到缓存.
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">
        /// 是否包含返回值参数
        /// </param>
        /// <returns>返回MySqlParameter参数数组</returns>
        internal static MySqlParameter[] GetSpParameterSet(MySqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            using (MySqlConnection clonedConnection = (MySqlConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// [私有]返回指定的存储过程的参数集(使用连接对象)
        /// </summary>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="includeReturnValueParameter">是否包含返回值参数</param>
        /// <returns>返回MySqlParameter参数数组</returns>
        private static MySqlParameter[] GetSpParameterSetInternal(MySqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
            MySqlParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as MySqlParameter[];
            if (cachedParameters == null)
            {
                MySqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);
        }

        #endregion 参数集检索结束
    }
}
